# （一）个税（固定QG PQ 改变 PEP EG,GDP减少）
# 第一步：在的到原始数据集和要计算的SAM指标及其计算方法的基础上利用PYTHON软件先计算SAM指标值，然后的到未调平的SAM表，最后进行RAS调平后输出EXCEL。
# 第二步：利用GAMS程序输入RAS调平后的SAM表（即第一步得到的EXCEL文件），导入GAMS文件中
# 第三步：导入RAS调平后的SAM表之后，运行个税GAMS代码得到所需数据。

# coding=utf-8
# import xlwt
# import xlsxwriter
import pandas as pd

# 读入原始数据
dfO = pd.read_excel(r"gams/source.xlsx", "SheetO")
# 读入所需SAM指标文件
dfSAM = pd.read_excel(r"gams/source.xlsx", "SAM")

# 得到原始数据集
odic = {}
okey = dfO["原始数据代码"].tolist()
ovalue = dfO["原始数据值"].tolist()

# 将原始数据设置为全局变量
for i in range(len(okey)):
    globals()[okey[i]] = ovalue[i]

# 得到sam表指标代码及计算公式和所用指标
samdicC = {}  # 定义sam指标和计算公式字典
samkey = dfSAM["SAM数据指标代码"]
samvalueC = dfSAM["计算公式"]
samlength = len(samkey)
for i in range(samlength):
    if samkey[i] not in samdicC:
        samdicC[samkey[i]] = samvalueC[i]

ls = []
for i in range(samlength):
    ls.append(eval(samvalueC[i]))
    globals()[samkey[i]] = ls[i]  # 将计算得到的SAM指标设置为全局变量方便计算平衡项

#########################################################
# 以下生成未调平的SAM表
# 定义列名行名生成空数据框
nameR = ["商品", "活动", "劳动力", "资本", "居民", "企业", "地方政府补贴", "中央政府补贴", "预算外", "地方政府", "中央政府", "ROW", "ROWC", "资本账户", "合计",
         "行目标值"]
nameC = ["商品", "活动", "劳动力", "资本", "居民", "企业", "地方政府补贴", "中央政府补贴", "预算外", "地方政府", "中央政府", "ROW", "ROWC", "资本账户", "合计",
         "列目标值"]
SAM0 = pd.DataFrame(index=nameR, columns=nameC)

# 将数据填入SAM表中
SAM0.iloc[0, 1] = YINT
SAM0.iloc[4, 2] = YYLS
SAM0.iloc[3, 1] = YYK
SAM0.iloc[6, 1] = Ytransfrslgov
SAM0.iloc[7, 1] = Ytransfrscgov
SAM0.iloc[9, 1] = YLGPT
SAM0.iloc[10, 1] = YCGPT
SAM0.iloc[1, 11] = YQEF
SAM0.iloc[1, 12] = YQEP
SAM0.iloc[1, 0] = YQDA
SAM0.iloc[8, 1] = YEBC

SAM0.iloc[4, 3] = YYHK
SAM0.iloc[5, 3] = YCCG
SAM0.iloc[11, 3] = YFII

SAM0.iloc[9, 5] = YLDT
SAM0.iloc[10, 5] = YCDT

SAM0.iloc[10, 0] = YTM
SAM0.iloc[11, 0] = YMF
SAM0.iloc[12, 0] = YMP
SAM0.iloc[10, 0] = YTM
SAM0.iloc[11, 0] = YMF
SAM0.iloc[12, 0] = YMP
SAM0.iloc[0, 4] = YQH
SAM0.iloc[0, 8] = YSFP
SAM0.iloc[0, 9] = YQLGOV
SAM0.iloc[0, 10] = YQCGOV
SAM0.iloc[1, 11] = YQEF
SAM0.iloc[1, 12] = YQEP
SAM0.iloc[0, 13] = YFIXK + YQS
SAM0.iloc[13, 13] = YQS

SAM0.iloc[2, 1] = YYLD

SAM0.iloc[9, 4] = YHTRL
SAM0.iloc[10, 4] = YHTRC
SAM0.iloc[13, 4] = YVYH
SAM0.iloc[4, 9] = Ytransfrhgov
SAM0.iloc[4, 5] = Ytransfrenth

SAM0.iloc[9, 10] = YCTP
SAM0.iloc[10, 9] = YPTC

SAM0.iloc[13, 5] = YCOS

SAM0.iloc[6, 1] = Ytransfrslgov
SAM0.iloc[6, 9] = Ytransfrslgov * (-1)

SAM0.iloc[7, 10] = Ytransfrscgov * (-1)

SAM0.iloc[13, 9] = YLGOVS
SAM0.iloc[13, 10] = YCGOVS
SAM0.iloc[13, 11] = YFS
SAM0.iloc[13, 12] = YIPS
SAM0.iloc[13, 8] = YEBAB

SAM0 = SAM0.fillna(0)

# 对列求和
SAM0.loc["合计"] = SAM0.sum()

# 对行求和
for i in range(len(SAM0)):
    SAM0["合计"][i] = SAM0.iloc[i, 0:14].sum()
SAM0.iloc[14, 14] = 0

SAM0.loc["行目标值"] = SAM0["合计"]
SAM0["列目标值"] = SAM0["合计"]
SAM0.insert(0, "c", nameC)

#####################################################################
# RAS调平

import pandas as pd
import numpy as np

# 预处理 首先将固定资本形成、存货变动合成资本账户一列

df = SAM0
df = df.iloc[:-2, :-2]  # 切掉合计、目标值的部分

# 获取负值数据的位置信息，并换成0
row_n = []
col_n = []
val_posi = []
for i in range(14):
    for j in range(1, 15):
        if df.iloc[i, j] < 0:
            val_posi.append(-df.iloc[i, j])
            row_n.append(i)
            col_n.append(j)
            df.iloc[i, j] = 0

# 将负值替换到对应位置
for i in range(len(col_n)):
    df.iloc[col_n[i] - 1, row_n[i] + 1] = val_posi[i]

col_sum = []
for i in range(0, 14):
    col_sum.append(df.iloc[i, 1:15].sum())

df.insert(15, '合计', col_sum)

df.insert(16, '列目标值', col_sum)

row_sum = []
for i in range(1, 15):
    row_sum.append(df.iloc[:, i].sum())

# row_sum

row_sum.insert(0, '合计')

row_sum.append(np.nan)
row_sum.append(np.nan)

df.loc[14] = row_sum

col_sum.insert(0, '行目标值')
col_sum.append(np.nan)
col_sum.append(np.nan)
df.loc[15] = col_sum


# df
def col_ras():  # 列调整一次
    col_dis = []
    for i in range(1, 15):
        sum_col = df.iloc[:-2, i].sum()
        df.iloc[:-2, i] = (df.iloc[:-2, i]) * df.iloc[-1, i] / sum_col
        col_dis.append(abs(df.iloc[:-2, i].sum() - df.iloc[-1, i]))
    return max(col_dis)


def row_ras():  # 行调整一次
    row_dis = []
    for i in range(0, 14):
        sum_row = df.iloc[i, 1:15].sum()
        df.iloc[i, 1:15] = (df.iloc[i, 1:15]) * df.iloc[i, -1] / sum_row
        row_dis.append(abs(df.iloc[i, 1:15].sum() - df.iloc[i, -1]))
    return max(row_dis)


maxdis = 0.1
iter1 = 1
while (iter1 < 5000 and maxdis > 1e-10):  # 迭代5000次或者插值小于1e-10次时退出循环 调整完成
    a = col_ras()
    b = row_ras()
    maxdis = max(a, b)  # 计算最大的差值
    iter1 = iter1 + 1
for i in range(1, 15):
    df.iloc[-2, i] = df.iloc[:-2, i].sum()
for i in range(0, 14):
    df.iloc[i, -2] = df.iloc[i, 1:15].sum()

# df
# negative_n=[] 负值放回去
for i in range(len(col_n)):
    temp = -df.iloc[col_n[i] - 1, row_n[i] + 1]
    df.iloc[col_n[i] - 1, row_n[i] + 1] = 0
    df.iloc[row_n[i], col_n[i]] = temp

for i in range(1, 15):
    df.iloc[-2, i] = df.iloc[:-2, i].sum()
for i in range(0, 14):
    df.iloc[i, -2] = df.iloc[i, 1:15].sum()

df = df.iloc[:-1, :-1]

df.iloc[:-1, :-1] = df.iloc[:-1, :-1].replace(0, np.nan)

df = df.drop(["c"], axis=1)

df.to_excel('gams/rasSAM.xlsx', index=False)  # 输出到当前工作路径
